USE [Weval Test]
GO
/****** Object:  StoredProcedure [dbo].[EssayAnswers]    Script Date: 05/12/2014 16:25:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[EssayAnswers] @CRN int, @Year int, @semester int

AS
--DECLARE @CRN int = 30626, @semester int = 3, @year int = 2013, @testID int = 64529;
DECLARE @testID int = 
(
	SELECT DISTINCT TOP 1 T.ID
	FROM Tests T
		INNER JOIN SectionTests ST ON T.ID = ST.testID
		INNER JOIN Results R ON ST.deliveryID = R.deliveryID
		INNER JOIN CourseSections CS ON R.sectionID = CS.ID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester = @semester
			AND typeID = 5
);

SELECT
cs.ID,
	CS.bannerCRN AS CRN,
	CS.subjectCode,
	CS.courseNumber,
	CS.semester,
	CS.block,
	Q.ID,
	Qr.ID,
	Q.text AS Question,
	coalesce(ER.essayResponse, 'No Response') AS Answer,
	DENSE_RANK() OVER(ORDER BY R.StudentID) Student
FROM SectionsTaught ST
	INNER JOIN Results R ON R.sectionID = ST.sectionID
		--AND (R.deleted = 0 OR R.deleted IS NULL)
		--AND R.startTime BETWEEN DATEADD(year, -5, GETDATE()) AND GETDATE() --Last 5 years
		--AND ST.instructorID = 887969243
	INNER JOIN CourseSections CS ON CS.ID = R.sectionID
		AND CS.bannerCRN = @CRN
		AND CS.year = @year
		AND CS.semester = @semester
	INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
	INNER JOIN Questions Q ON Q.ID = QR.questionID
	INNER JOIN QuestionTypes QT on Q.typeID = Qt.ID and name = 'Essay' 
	INNER JOIN Tests T ON T.ID = Q.testID 
		--AND T.typeID = 5 --Course Eval
		AND T.ID = @testID
	LEFT JOIN EssayResponses ER ON ER.questionResponseID = QR.ID
		--AND ER.essayResponse IS NOT NULL
ORDER BY student, Q.ID